package com.estar.report.util;

import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.jeecgframework.web.cgreport.common.CgReportConstant;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.jeecgframework.core.util.StringUtil;

/**
 * 
 * @Title:QueryParamUtil
 * @description:动态报表查询条件处理工具
 * @author 赵俊夫
 * @date Jul 5, 2013 10:22:31 PM
 * @version V1.0
 */
public class ReportQueryParamUtil extends  org.jeecgframework.web.cgform.util.QueryParamUtil

{
	/**
	 * 组装查询参数
	 * @param request 请求(查询值从此处取)
	 * @param item 动态报表字段配置
	 * @param params 参数存放
	 */
	@SuppressWarnings("unchecked")
	public static void loadQueryParams(HttpServletRequest request, Map item, Map params) throws UnsupportedEncodingException {
		String filedName = (String) item.get(CgReportConstant.ITEM_FIELDNAME);//名称
		String queryMode = (String) item.get(CgReportConstant.ITEM_QUERYMODE);//查询模式：范围 or单选
		String filedType = (String) item.get(CgReportConstant.ITEM_FIELDTYPE);//item的类型
		if("single".equals(queryMode)){
			//单条件组装方式
			String value =request.getParameter(filedName.toLowerCase());//从页面获取参数值
			if(filedType.equals("Date")){
				String month=request.getParameter(filedName.toLowerCase()+"_month");
				if(StringUtil.isEmpty(month)){
					SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
					Date date=new Date();
					month=sdf.format(date);
				}
				String begin = month+"-01";
				String end = month+"-"+selectDate(month);
				group(request,begin,end, filedName, filedType, params);
			}
			if(StringUtil.isEmpty(value)){//为空就返回
				return;
			}
			String uri = request.getQueryString();
			if(uri.contains(filedName+"=")){
				String contiansChinesevalue = value;
				value = contiansChinesevalue;
			} 
			sql_inj_throw(value);
			value = applyType(filedType,value);
			if(!StringUtil.isEmpty(value)){
				if(value.contains("*")){
					//模糊查询
					value = value.replaceAll("\\*", "%");
					params.put(filedName, CgReportConstant.OP_LIKE+value);
				}else{
					params.put(filedName, CgReportConstant.OP_EQ+value);
				}
			}
		}else if("group".equals(queryMode)){
			String begin = request.getParameter(filedName.toLowerCase()+"_begin");
			if(StringUtil.isEmpty(begin)){
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
				Date date=new Date();
				begin=sdf.format(date)+"-01";
			}
			String end = request.getParameter(filedName.toLowerCase()+"_end");
			if(StringUtil.isEmpty(end)){
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
				Date date=new Date();
				String month=sdf.format(date);
				end = month+"-"+selectDate(month);
				
			}
			group(request,begin,end, filedName, filedType, params);
		}
	}
	
	public static void group(HttpServletRequest request,String begin, String end ,String filedName, String filedType,Map params){
		//范围查询组装
		sql_inj_throw(begin);
		begin= applyType(filedType,begin);//根据字段组装成TO_DATE('2016-12-01','yyyy-MM-dd')
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		if(end!=null && !end.equals("")){
			Date endDay=null;
			try {
				endDay = sdf.parse(end);
				Calendar cal = Calendar.getInstance();
				cal.setTime(endDay);
				if(!filedName.equals("W.WORKDATE")){
					cal.add(Calendar.DATE, 1);//加一天
				}
				endDay=cal.getTime();
				end=sdf.format(endDay);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		sql_inj_throw(end);
		end= applyType(filedType,end);
		if(!StringUtil.isEmpty(begin)){
			String re = " BETWEEN "+begin;
			if(!StringUtil.isEmpty(end)){
				re +=" AND "+end;
			}
			params.put(filedName, re);
		}else if(!StringUtil.isEmpty(end)){
			String re = CgReportConstant.OP_LQ+end;
			params.put(filedName, re);
		}
	}
	public static String selectDate(String date){
		
		int year=Integer.parseInt(date.substring(0, 4));
		int month=Integer.parseInt(date.substring(5, date.length()));
		byte a=(byte) month;
		switch (a) {
		case 1:case 3:case 5:case 7:case 8:case 10:case 12:
			return "31";
			
		case 4:case 6:case 9:case 11:
			return "30";
		case 2:
			if(year%4==0&&year%100!=0||year%400==0){
				return "29";
			}else{
				return "28";
			}
		
		default:
			return "01";
		}
		
	}
	
	/**
	 * 将结果集转化为列表json格式
	 * @param result 结果集
	 * @param size 总大小
	 * @return 处理好的json格式
	 */
	@SuppressWarnings("unchecked")
	public static String getJson(List<Map<String, Object>> result,Long size,String addTitle){
		JSONObject main = new JSONObject();
		JSONArray rows = new JSONArray();
		main.put("total",size );
		for(Map m:result){
			JSONObject item = new JSONObject();
			Iterator  it =m.keySet().iterator();
			while(it.hasNext()){
				String key = (String) it.next();
				String value =String.valueOf(m.get(key));
				key = key.toLowerCase();
				if(key.contains("time")||key.contains("date")){
					value = datatimeFormat(value);
				}
				item.put(key,value );
			}
			rows.add(item);
		}
		main.put("a",addTitle );
		main.put("rows", rows);
		return main.toString();
	}
	public static String getFullSql(String sql,Map params){
		
		StringBuilder sqlB =  new StringBuilder();
		sqlB.append("SELECT rr.* FROM ( ");
		sqlB.append(sql+" ");
		sqlB.append(") rr ");
		if (params.size() >= 1) {
			sqlB.append("WHERE 1=1  ");
			Iterator it = params.keySet().iterator();
			while (it.hasNext()) {
				String key = String.valueOf(it.next());
				String value = String.valueOf(params.get(key));
				if (!StringUtil.isEmpty(value) && !"null".equals(value)) {
						sqlB.append(" AND ");
						sqlB.append(" " + key +  value );
				}
			}
		}
		return sqlB.toString();
	}
	
	/*
	 * 标题显示时间
	 * */
	public static String getTimeForName(HttpServletRequest request,List<Map<String,Object>> items,String area,String depart,String com){
		String addTitle="";
		String filedName=null;
		String m=null;
		String begin=null;
		String end=null;
		String company=null;
		for(Map<String,Object> item:items){
			String isQuery = (String) item.get(CgReportConstant.ITEM_ISQUERY);//寻找哪个字段需要查询
			if(CgReportConstant.BOOL_TRUE.equalsIgnoreCase(isQuery)){
				if(item.get(CgReportConstant.ITEM_FIELDTYPE).equals("Date") && item.get(CgReportConstant.ITEM_QUERYMODE).equals("group")){
					 filedName = (String) item.get(CgReportConstant.ITEM_FIELDNAME);
					  begin=request.getParameter(filedName.toLowerCase()+"_begin");
					end=request.getParameter(filedName.toLowerCase()+"_end");
				}
				
				if(item.get(CgReportConstant.ITEM_FIELDTYPE).equals("Date") && item.get(CgReportConstant.ITEM_QUERYMODE).equals("single")){
					filedName = (String) item.get(CgReportConstant.ITEM_FIELDNAME);
					m=request.getParameter(filedName.toLowerCase()+"_month");
				}
				
				if(item.get(CgReportConstant.ITEM_FIELDTYPE).equals("String") && item.get(CgReportConstant.ITEM_QUERYMODE).equals("single")){
						if(item.get(CgReportConstant.ITEM_FIELDNAME).equals("tmp8.departname")){//加上保险公司的名字，写死了
							filedName = (String) item.get(CgReportConstant.ITEM_FIELDNAME);
							company=request.getParameter(filedName);
					}
				}
			}
		}
		if(StringUtil.isNotEmpty(m)){
			addTitle=m+"月-";
		}else if(StringUtil.isNotEmpty(begin) && StringUtil.isNotEmpty(end)){
			addTitle=begin+"到"+end+"-";
		}else if(StringUtil.isNotEmpty(begin) && StringUtil.isEmpty(end)){
			SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM-dd");
			Date date=new Date();
			addTitle=begin+"到"+sd.format(date)+"-";
		}else if(StringUtil.isEmpty(addTitle)){
			SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM");
			Date date=new Date();
			addTitle=sd.format(date)+"月-";
		}
		if(StringUtil.isNotEmpty(com)){
			addTitle+=com+"-";
		}
		if(StringUtil.isNotEmpty(depart)){
			if(depart.equals("A04A01")){
				addTitle+="-广州-";
			}else if(depart.equals("A04A02")){
				addTitle+="-佛山-";
			}
		}
		if(StringUtil.isNotEmpty(area)){
			if(area.equals("1")){
				addTitle+="-城区-";
			}else if(area.equals("2")){
				addTitle+="-高速-";
			}
		}
		if(StringUtil.isNotEmpty(company)){
			addTitle+=company;
		}
		return addTitle;
	}
	
}
